/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.soc.cs5226.gui.reports.beans;

import edu.soc.cs5226.gui.entities.*;
import edu.soc.cs5226.gui.entities.util.Constants;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 *
 * @author Huimin
 */
public class ReportsDAO {
    
    public static java.util.Collection generateSPSCollection(Timestamp stDate, Timestamp edDate)throws SQLException, NamingException {
        java.util.Vector collection = new java.util.Vector();
        
        SharedPool spsObject = null;
                
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        
        System.out.println("SPS startdate: " + stDate);
        System.out.println("enddate: " + edDate);
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select dateTime, pinHits, pins, hitRatio, sps_healthStatus from statistics "
                    + "where dateTime between ? and ?");
            
            pstmt.setTimestamp(1, stDate);
            pstmt.setTimestamp(2, edDate);
            
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                
                spsObject = new SharedPool();
                spsObject.setdateTime(rs.getTimestamp(1));
                spsObject.setpinHits(rs.getLong(2));
                spsObject.setpins(rs.getLong(3));
                spsObject.sethitRatio(rs.getLong(4));
                spsObject.setsps_healthStatus(rs.getString(5));
                
                collection.add(spsObject);
            } //
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving configuration");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return collection;
        }             
    }
 
    public static java.util.Collection generateBCCollection(Timestamp stDate, Timestamp edDate)throws SQLException, NamingException {
        java.util.Vector collection = new java.util.Vector();
        
        BufferCache bcObject = null;
                
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        
        System.out.println("BC startdate: " + stDate);
        System.out.println("enddate: " + edDate);
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select dateTime, dBBlockGets, consistentGets, physicalReads, cacheHitRatio, "
                    + "bc_healthStatus from statistics where dateTime between ? and ?");
            
            pstmt.setTimestamp(1, stDate);
            pstmt.setTimestamp(2, edDate);
            
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                
                bcObject = new BufferCache();
                bcObject.setdateTime(rs.getTimestamp(1));
                bcObject.setDbBlockGets(rs.getLong(2));
                bcObject.setConsistentGets(rs.getLong(3));
                bcObject.setPhysicalReads(rs.getLong(4));
                bcObject.setCacheHitRatio(rs.getLong(5));
                bcObject.setBc_healthStatus(rs.getString(6));
                
                collection.add(bcObject);
            } //
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving configuration");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return collection;
        }             
    }
    
    public static java.util.Collection generateLBCollection(Timestamp stDate, Timestamp edDate)throws SQLException, NamingException {
        java.util.Vector collection = new java.util.Vector();
        
        RedoLogBuffer lbObject = null;
                
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        
        System.out.println("LB startdate: " + stDate);
        System.out.println("enddate: " + edDate);
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select dateTime, redoLogSpaceRequest, "
                    + "lb_healthStatus from statistics where dateTime between ? and ?");
            
            pstmt.setTimestamp(1, stDate);
            pstmt.setTimestamp(2, edDate);
            
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                
                lbObject = new RedoLogBuffer();
                lbObject.setdateTime(rs.getTimestamp(1));
                lbObject.setRedoLogSpaceRequest(rs.getLong(2));
                lbObject.setLb_healthStatus(rs.getString(3));
                
                collection.add(lbObject);
            } //
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving configuration");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return collection;
        }             
    }
    
    public static java.util.Collection generateSCollection(Timestamp stDate, Timestamp edDate)throws SQLException, NamingException {
        java.util.Vector collection = new java.util.Vector();
        
        SortMemory sObject = null;
                
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        
        System.out.println("S startdate: " + stDate);
        System.out.println("enddate: " + edDate);
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select dateTime, sortsMemory, sortsDisk, sortingToDisk, "
                    + "s_healthStatus from statistics where dateTime between ? and ?");
            
            pstmt.setTimestamp(1, stDate);
            pstmt.setTimestamp(2, edDate);
            
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                
                sObject = new SortMemory();
                sObject.setdateTime(rs.getTimestamp(1));
                sObject.setSortsMemory(rs.getLong(2));
                sObject.setSortsDisk(rs.getLong(3));
                sObject.setSortingToDisk(rs.getLong(4));
                sObject.setS_healthStatus(rs.getString(5));
                
                collection.add(sObject);
            } //
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving configuration");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return collection;
        }             
    }
    
    public static java.util.Collection generateRSCollection(Timestamp stDate, Timestamp edDate)throws SQLException, NamingException {
        java.util.Vector collection = new java.util.Vector();
        
        RollbackSegment rsObject = null;
                
        //Retrieve Data from Database
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(Constants.JAVADB);
        Connection conn = ds.getConnection();
        
        System.out.println("RS startdate: " + stDate);
        System.out.println("enddate: " + edDate);
        try {
            PreparedStatement pstmt = conn.prepareStatement ("select dateTime, waits, gets, rollbackSegment, "
                    + "rs_healthStatus from statistics where dateTime between ? and ?");
            
            pstmt.setTimestamp(1, stDate);
            pstmt.setTimestamp(2, edDate);
            
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                
                rsObject = new RollbackSegment();
                rsObject.setdateTime(rs.getTimestamp(1));
                rsObject.setWaits(rs.getLong(2));
                rsObject.setGets(rs.getLong(3));
                rsObject.setRollbackSegment(rs.getLong(4));
                rsObject.setRs_healthStatus(rs.getString(5));
                
                collection.add(rsObject);
            } //
        }
        catch (SQLException se)
        {
            System.out.println("SQL exception in retrieving configuration");
            se.printStackTrace();
        }
        finally {
            conn.close();
            return collection;
        }             
    }
}
